-- @owner: opentestcase013
-- @date: 2023/9/27
-- @testpoint: connect by 查询中使用同义词

--step1:建表并插入数据;expect:成功
drop table if exists t_connectby_0037 cascade;
create table t_connectby_0037 (
  org_parent_no varchar2(32),
  org_no varchar2(32) not null,
  org_rela_type varchar2(32) not null
);

insert into t_connectby_0037 (
  org_no, org_parent_no, org_rela_type
)
values
  (
    '201855', '201844', 'administaration'
  ),
  (
    '201856', '201844', 'administaration'
  ),
  (
    '119208', '119200', 'administaration'
  ),
  (
    '201953', '201932', 'administaration'
  ),
  (
    '201954', '201932', 'administaration'
  ),
  (
    '201955', '201932', 'administaration'
  ),
  (
    '201956', '201932', 'administaration'
  ),
  (
    '120301', '120300', 'administaration'
  ),
  (
    '201957', '202573', 'administaration'
  ),
  (
    '201958', '201957', 'administaration'
  );


--step2:创建同义词;expect:成功
drop synonym if exists sy_connectby_0037 cascade;
create synonym sy_connectby_0037 for t_connectby_0037;

--step3:connect by查询;expect:成功
select
  org_no
from
  t_connectby_0037 start with org_no = '201957'
  and org_rela_type = 'administaration' connect by prior org_no = org_parent_no
  and org_rela_type = 'administaration';

--step4:connect by使用同义词查询;expect:成功
select
  org_no
from
  sy_connectby_0037 start with org_no = '201957'
  and org_rela_type = 'administaration' connect by prior org_no = org_parent_no
  and org_rela_type = 'administaration';

--step5:清理环境;expect:成功
drop table if exists t_connectby_0037 cascade;
drop synonym if exists sy_connectby_0037 cascade;